﻿using OfficeOpenXml;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace OfficeHelper.Help
{
    public static class EEPlusHelp
    {
        /// <summary>
        /// 获取单元格字符串的值 默认为string.Empty
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static string GetCellsValueString(this ExcelWorksheet wSheet, int row, int column)
        {
            var val = wSheet.Cells[row, column].Value;
            if (val != null)
            {
                return val.ToString();
            }
            return string.Empty;
        }

        /// <summary>
        /// 获取单元格字符串的值 默认为string.Empty   
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="loaction"></param>
        /// <returns></returns>
        public static string GetCellsValueString(this ExcelWorksheet wSheet, string loaction)
        {
            var val = wSheet.Cells[loaction].Value;
            if (val != null)
            {
                return val.ToString();
            }
            return string.Empty;
        }


        /// <summary>
        /// 获取合并单元格 Decimal值
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static decimal? GetCellsValueDecimal(this ExcelWorksheet wSheet, int row, int column)
        {
            decimal result = Decimal.Zero;

            var val = wSheet.Cells[row, column].Value;
            if (val != null)
            {
                bool isOk = Decimal.TryParse(val.ToString(), out result);
                if (isOk)
                {
                    return result;
                }
            }

            return null;
        }

        /// <summary>
        /// 获取合并单元格 Decimal值
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static int? GetCellsValueInt(this ExcelWorksheet wSheet, int row, int column)
        {
            int result;

            var val = wSheet.Cells[row, column].Value;
            if (val != null)
            {
                bool isOk = int.TryParse(val.ToString(), out result);
                if (isOk)
                {
                    return result;
                }
            }

            return null;
        }


        /// <summary>
        /// 获取合并单元格 double
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static double? GetCellsValueDouble(this ExcelWorksheet wSheet, int row, int column)
        {
            double result;

            var val = wSheet.Cells[row, column].Value;
            if (val != null)
            {
                bool isOk = double.TryParse(val.ToString(), out result);
                if (isOk)
                {
                    return result;
                }
            }

            return null;
        }


        /// <summary>
        /// 获取合并单元格 Decimal值 默认为0
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static DateTime? GetCellsValueDatetime(this ExcelWorksheet wSheet, int row, int column)
        {
            DateTime result = DateTime.MinValue;

            var val = wSheet.Cells[row, column].Value;
            if (val != null)
            {
                bool isOK = DateTime.TryParse(val.ToString(), out result);
                if (isOK)
                {
                    return result;
                }
            }
            return null;
        }

        /// <summary>
        /// 获取合并单元格的值
        /// </summary>
        /// <param name="wSheet"></param>
        /// <param name="row"></param>
        /// <param name="column"></param>
        /// <returns></returns>
        public static string GetMegerValue(this ExcelWorksheet wSheet, int row, int column)
        {
            string range = wSheet.MergedCells[row, column];
            if (range == null)
                if (wSheet.Cells[row, column].Value != null)
                    return wSheet.Cells[row, column].Value.ToString();
                else
                    return "";
            object value =
                wSheet.Cells[(new ExcelAddress(range)).Start.Row, (new ExcelAddress(range)).Start.Column].Value;
            if (value != null)
                return value.ToString();
            else
                return "";
        }
    }
}
